package org.ezplatform.office.rd.archives.dao;

import org.ezplatform.core.dao.jpa.JpaBaseDao;
import org.ezplatform.office.rd.archives.entity.ArchiveEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("sdagArchiveDao")
public abstract interface SdagArchiveDao extends JpaBaseDao<ArchiveEntity, String> {
    //根据当前表单id查出关联附件id
    @Query(nativeQuery = true,value="select replace(ATTACHMENT,'|',',') from goa_doc_receivefile where id=?1")
    public abstract String getFileIdById(String id);

    @Query(nativeQuery = true,value="select file_displayname,file_name,file_size,file_ext from sys_attachment where id in(?1)")
    public abstract List<Map> getFjInfos(String ids);

    //根据附件表id查附件保存名
    @Query(nativeQuery = true,value="select file_name from sys_attachment where id=?1")
    public abstract String getFileNameById(String id);

    //根据发文scope表查中文名
    @Query(nativeQuery = true,value="select STRING_AGG(name, ',') from goa_doc_sendfile_scope where FIELDDATAVALUE=?1")
    public abstract String getScopeName(String id);

    //根据processinstance_id获取签发人及签发日期
    @Query(nativeQuery = true,value="select user_name,to_char(signtime,'yyyymmdd') as signtime from flexoffice.bpm_process_comment where field='goa_doc_sendfile_sign_send_opinion' and processinstance_id=?1 ")
    public abstract List<Map> getSignInfos(String pid);

    //获取发文记录表
    @Query(nativeQuery = true,value="select to_char(createddate,'yyyymmdd') as createddate,file_displayname,file_name,file_size,file_ext,relative_path from flexoffice.sys_attachment where id=(select FILE_ID from flexoffice.goa_doc_file_edition where recordid=?1 order by createddate desc LIMIT 1 )")
    public abstract List<Map> getContentInfos(String recordid);
    //版式正文附件信息
    @Query(nativeQuery = true,value="select to_char(createddate,'yyyymmdd') as createddate,file_displayname,file_name,file_size,file_ext,relative_path from flexoffice.sys_attachment where file_name=?1 or file_name=?2")
    public abstract List<Map> getBsInfos(String ofd, String pdf);

    @Query(nativeQuery = true,value="select to_char(createddate,'yyyymmdd') as createddate,file_displayname,file_name,file_size,file_ext,relative_path from flexoffice.sys_attachment where id in (select FILE_ID from flexoffice.goa_doc_file_edition where goa_id=?1 ) and file_ext!='.ofd' order by createddate desc")
    public abstract List<Map> getLsInfos(String recordid);

    //根据发文正文表获取正文信息
    @Query(nativeQuery = true,value="select to_char(createddate,'yyyymmdd') as createddate,file_displayname,file_name,file_size,file_ext,relative_path from flexoffice.sys_attachment where file_name=(select filename from cmp_gg_document_file where recordid=?1 LIMIT 1 ) ")
    public abstract List<Map> getLsZwInfos(String recordid);


    //根据id查归档结果反馈表（含退回）
    @Query(nativeQuery = true,value="select person,govtype,reason,rq,bz,flag,repush from gdjgfkb where govid =?")
    public abstract List<Map> getFeedback(String id);

    //收文正文附件信息
    @Query(nativeQuery = true,value="select to_char(createddate,'yyyymmdd') as createddate,file_displayname,file_name,file_size,file_ext,relative_path from flexoffice.sys_attachment where file_name=?1 ")
    public abstract List<Map> getReceiveZwInfos(String fileName);
}
